﻿CREATE PROCEDURE [dbo].[yaf_user_list](
                @BoardID  INT,
                @UserID   INT  = NULL,
                @Approved BIT  = NULL,
                @GroupID  INT  = NULL,
                @RankID   INT  = NULL)
AS
    BEGIN
        IF @UserID IS NOT NULL
        SELECT   a.*,
                 a.NumPosts,
                 b.RankID,
                 RankName = b.Name,
                 NumDays = Datediff(d,a.Joined,Getdate()) + 1,
                 NumPostsForum = (SELECT COUNT(1)
                                  FROM   yaf_Message x
                                  WHERE  (x.Flags & 24) = 16),
                 HasAvatarImage = (SELECT COUNT(1)
                                   FROM   yaf_User x
                                   WHERE  x.UserID = a.UserID
                                   AND AvatarImage IS NOT NULL),
                 IsAdmin = Isnull(c.IsAdmin,0),
                 IsGuest = Isnull(c.IsGuest,0),
                 IsForumModerator = Isnull(c.IsForumModerator,0),
                 IsModerator = Isnull(c.IsModerator,0)
        FROM     yaf_User a
                 JOIN yaf_Rank b
                   ON b.RankID = a.RankID
                 LEFT JOIN yaf_vaccess c
                   ON c.UserID = a.UserID
        WHERE    a.UserID = @UserID
        AND a.BoardID = @BoardID
        AND Isnull(c.ForumID,0) = 0
        AND (@Approved IS NULL 
              OR (@Approved = 0
                  AND (a.Flags & 2) = 0)
              OR (@Approved = 1
                  AND (a.Flags & 2) = 2))
        ORDER BY a.Name
        ELSE
        IF @GroupID IS NULL 
           AND @RankID IS NULL
        SELECT   a.*,
                 a.NumPosts,
                 IsAdmin = (SELECT COUNT(1)
                            FROM   yaf_UserGroup x,
                                   yaf_Group y
                            WHERE  x.UserID = a.UserID
                            AND y.GroupID = x.GroupID
                            AND (y.Flags & 1) <> 0),
                 b.RankID,
                 RankName = b.Name
        FROM     yaf_User a
                 JOIN yaf_Rank b
                   ON b.RankID = a.RankID
        WHERE    a.BoardID = @BoardID
        AND (@Approved IS NULL 
              OR (@Approved = 0
                  AND (a.Flags & 2) = 0)
              OR (@Approved = 1
                  AND (a.Flags & 2) = 2))
        ORDER BY a.Name
        ELSE
        SELECT   a.*,
                 a.NumPosts,
                 IsAdmin = (SELECT COUNT(1)
                            FROM   yaf_UserGroup x,
                                   yaf_Group y
                            WHERE  x.UserID = a.UserID
                            AND y.GroupID = x.GroupID
                            AND (y.Flags & 1) <> 0),
                 b.RankID,
                 RankName = b.Name
        FROM     yaf_User a
                 JOIN yaf_Rank b
                   ON b.RankID = a.RankID
        WHERE    a.BoardID = @BoardID
        AND (@Approved IS NULL 
              OR (@Approved = 0
                  AND (a.Flags & 2) = 0)
              OR (@Approved = 1
                  AND (a.Flags & 2) = 2))
        AND (@GroupID IS NULL 
              OR EXISTS (SELECT 1
                         FROM   yaf_UserGroup x
                         WHERE  x.UserID = a.UserID
                         AND x.GroupID = @GroupID))
        AND (@RankID IS NULL 
              OR a.RankID = @RankID)
        ORDER BY a.Name
    END


